#!/usr/bin/env python3

#ln -s $PWD/test.py /usr/local/bin/test

import json
import os
import re
import sys
# import pypyodbc
import operator
import xlsxwriter
import argparse

# python -m nuitka --follow-imports --standalone test.4C.py

local_path = os.getcwd()
target_path = R""

first_side = []
second_side = []
third_side = []


# 序号转Excel列名
def colnum_to_name(column: int) -> str:
    if column > 25:
        ch1 = chr(column % 26 + 65)
        ch2 = chr(int(column / 26) + 64)
        return ch2 + ch1
    else:
        return chr(column % 26 + 65)


# 匹配名称是否合法
def check_path(path: str):
    regex = "20[0-9]{10}_.*_.*_.*_*"
    if re.match(regex, path) is not None:
        return True
    return False


def parse_json_config() -> bool:
    jsonpath = json_file()
    if not os.path.exists(jsonpath):
        return False
    with open(jsonpath, "r") as f:
        doc = json.load(f)
        for i in range(1, 17):
            if str(i) in doc and doc[str(i)] == True:
                first_side.append(i)
        for i in range(21, 37):
            if str(i) in doc and doc[str(i)] == True:
                second_side.append(i)
        for i in range(41, 47):
            if str(i) in doc and doc[str(i)] == True:
                third_side.append(i)
    return True


def image_time(time: str) -> str:
    if len(time) == 9:
        return time[0:2]+":"+time[2:4]+":"+time[4:6]+"."+time[6:9]
    else:
        return time


# 保存对应数量图像的支柱图片信息
class ImageInfo:
    def __init__(self) -> None:
        self.count = 0
        self.from_first = 0
        self.from_second = 0
        self.from_all = 0
        
        
class RowInfo:
    def __init__(self) -> None:
        self.station = ""
        self.pillar = ""
        self.km = ""
        self.km_n = 0
        self.image_all = 0
        self.image_p = 0
        self.image_d = 0
        self.image_1 = 0
        self.image_2 = 0
        self.t_1_min = ""
        self.t_1_max = ""
        self.t_2_min = ""
        self.t_2_max = ""
        self.cameras = []


# 保存每个文件夹对应的图像数量
class PillarInfo:
    def __init__(self) -> None:
        self.first = 0
        self.second = 0
        self.third = 0


class XlsxReport:
    def __init__(self, output_path: str):
        self.workbook = xlsxwriter.Workbook(output_path)
        self.worksheet = self.workbook.add_worksheet("详细信息")
        self.cell_format = self.workbook.add_format({
            'bold': False,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 12,
        })

        self.header_format = self.workbook.add_format({
            'bold': True,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 12,
        })

        self.merge_format = self.workbook.add_format({
            'bold': False,
            'border': 1,
            'align': 'center',  # 水平居中
            'valign': 'vcenter',  # 垂直居中
            'font_size': 14,
        })

        self.num_format = self.workbook.add_format({
            'bold': False,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 12,
            'num_format': '0.00%'
        })

        self.time_format = self.workbook.add_format({
            'bold': False,
            'border': 1,
            'align': 'center',
            'valign': 'vcenter',
            'font_size': 12,
            'num_format': 'h:mm:ss.sss'
        })

        self.image_count_dic_1 = {}
        self.image_count_dic_2 = {}
        self.row_index = 1
        self.col_index = 1
        self.pillar_count = 0
        self.first_side_set = set()
        self.second_side_set = set()

        self.pillar_info = []
        self.stations = []

        if not parse_json_config():
            global first_side, second_side, third_side
            first_side = [i for i in range(1, 17)]
            second_side = [i for i in range(21, 37)]
            third_side = [i for i in range(41, 47)]

    def write_report_header(self):
        col_names = ["序号", "站区", "杆号", "公里标", "公里标", "图片数量", "支柱数量", "吊弦数量", "1位端数量", "2位端数量", "1位端最小时间", "1位端最大时间", "2位端最小时间", "2位端最大时间"]
        for index, name in enumerate(col_names):
            self.worksheet.write_string(0, index, name, self.header_format)

        self.col_index = len(col_names)
        for i in first_side:
            self.worksheet.write_string(0, self.col_index, "相机"+str(i), self.header_format)
            self.col_index += 1

        for i in second_side:
            self.worksheet.write_string(0, self.col_index, "相机"+str(i), self.header_format)
            self.col_index += 1

        for i in third_side:
            self.worksheet.write_string(0, self.col_index, "相机"+str(i), self.header_format)
            self.col_index += 1

        self.worksheet.set_column('B:B', 20)
        self.worksheet.set_column('D:I', 9)
        self.worksheet.set_column('J:N', 14)
        self.worksheet.set_row(0, 20)
        # 固定第一行
        self.worksheet.freeze_panes(1, 0, True)

    def write_total(self):
        row = self.row_index + 5
        self.worksheet.write_string(row, 0, "总计", self.cell_format)
        for i in range(5, 10):
            fmt = "=SUM({}2: {}{})".format(colnum_to_name(i), colnum_to_name(i), self.row_index)
            self.worksheet.write_formula(row, i, fmt, self.cell_format)
        for i in range(14, self.col_index):
            fmt = "=SUM({}2: {}{})".format(colnum_to_name(i), colnum_to_name(i), self.row_index)
            self.worksheet.write_formula(row, i, fmt, self.cell_format)

    def get_image_count(self, path: str):
        image_map = dict()
        first_side_max_time = ""
        first_side_min_time = ""
        second_side_max_time = ""
        second_side_min_time = ""
        try:
            files = os.listdir(path)
            for file in files:
                if file.endswith('.jpg'):
                    num = int(file[-6:-4])
                    time = file[0:9]
                    if num in image_map:
                        image_map[num] += 1
                    else:
                        image_map[num] = 1

                    if num in range(0, 20):
                        if first_side_min_time > time or first_side_min_time == "":
                            first_side_min_time = time
                        if first_side_max_time < time:
                            first_side_max_time = time
                        self.first_side_set.add(num)
                    elif num in range(20, 40):
                        if second_side_min_time > time or second_side_min_time == "":
                            second_side_min_time = time
                        if second_side_max_time < time:
                            second_side_max_time = time
                        self.second_side_set.add(num)
        except OSError as e:
            print("...")
            
            
        return image_map, first_side_max_time, first_side_min_time, second_side_max_time, second_side_min_time

    '''读取站区顺序'''

    # def read_index_mdb(self, path: str):
    #     db_path = os.path.join(path, "index.mdb")
    #     s = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + db_path + ""
    #     conn = pypyodbc.win_connect_mdb(s)

    #     sql = "select SETLOC,KM,ST,DETECTDATE from PICINDEX order by DETECTDATE asc"
    #     cur = conn.cursor()
    #     try:
    #         cur.execute(sql)
    #         result = cur.fetchall()
    #         for row in result:
    #             station = row[2]
    #             if station not in self.stations:
    #                 self.stations.append(station)
    #         return True
    #     except Exception as ex:
    #         print('An exception occurred')
    #         return False

    def start(self, path: str):
        '''如果读取出顺序的话'''
        # if self.read_index_mdb(path):
        #     stations = self.stations
        # else:
        stations = os.listdir(path)
            
        pillars = []
        
        for station in stations:
            print(station)
            station_path = os.path.join(target_path, station)
            if not os.path.isdir(station_path):
                continue
            km_pillars = os.listdir(station_path)

            for km_pillar in km_pillars:
                pos = km_pillar.find('_')
                km = km_pillar[0:pos]
                pillar = km_pillar[pos+1:]
                pillar_path = os.path.join(station_path, km_pillar)
                if pos > 0 and km_pillar[0] == 'K' and os.path.isdir(pillar_path):  # 杆号公里标
                    m, max_1, min_1, max_2, min_2 = self.get_image_count(pillar_path)
                    row = RowInfo()
                    row.station = station
                    row.pillar = pillar
                    row.km = km
                    row.km_n = int(km[1:])
                    s = sum(m.values())
                    row.image_all = sum(m.values())
                    m_1 = dict((k, v) for k, v in m.items() if k in range(0, 20))
                    m_2 = dict((k, v) for k, v in m.items() if k in range(20, 40))
                    m_3 = dict((k, v) for k, v in m.items() if k in range(40, 50))
                    count_1 = sum(m_1.values())
                    count_2 = sum(m_2.values())
                    row.image_p = count_1 + count_2
                    row.image_d  = sum(m_3.values())
                    row.image_1  = count_1
                    row.image_2  = count_2
                    row.t_1_min = image_time(min_1)
                    row.t_1_max = image_time(max_1)
                    row.t_2_min = image_time(min_2)
                    row.t_2_max= image_time(max_2)

                    info = PillarInfo()
                    info.first = count_1
                    info.second = count_2
                    info.third = sum(m_3.values())
                    self.pillar_info.append(info)

                    for i in first_side:
                        row.cameras.append(m_1[i] if i in m_1 else 0)
                        
                    for i in second_side:
                        row.cameras.append(m_2[i] if i in m_2 else 0)
                        
                    for i in third_side:
                        row.cameras.append(m_3[i] if i in m_3 else 0)

                    # ---------
                    pillars.append(row)

                    if count_1 in self.image_count_dic_1:
                        self.image_count_dic_1[count_1] += 1
                    else:
                        self.image_count_dic_1[count_1] = 1

                    if count_2 in self.image_count_dic_2:
                        self.image_count_dic_2[count_2] += 1
                    else:
                        self.image_count_dic_2[count_2] = 1

                    self.pillar_count += 1

        print("sort...")
        cmp = operator.attrgetter("t_1_min","t_2_min")
        pillars.sort(key=cmp)
        for p in pillars:
            cols = []
            cols.append(p.station)
            cols.append(p.pillar)
            cols.append(p.km)
            cols.append(p.km_n)
            cols.append(p.image_all)
            cols.append(p.image_p)
            cols.append(p.image_d)
            cols.append(p.image_1)
            cols.append(p.image_2)
            cols.append(p.t_1_min)
            cols.append(p.t_1_max)
            cols.append(p.t_2_min)
            cols.append(p.t_2_max)
            cols += p.cameras
            self.write_line(*cols)

    def write_line(self, *kw):
        self.worksheet.write_number(self.row_index, 0, self.row_index, self.cell_format)
        for index, value in enumerate(kw):
            if isinstance(value, int):
                self.worksheet.write_number(self.row_index, index+1, value, self.cell_format)
            else:
                if index in range(10, 14):
                    self.worksheet.write_string(self.row_index, index+1, value, self.time_format)
                else:
                    self.worksheet.write_string(self.row_index, index+1, value, self.cell_format)
        self.row_index += 1

    def statistics(self):
        self.sheet = self.workbook.add_worksheet("统计")
        self.workbook.worksheets_objs.reverse()  # sheet调整顺序，将此sheet修改为第一个
        self.sheet.write_string(0, 0, "支柱总数量", self.cell_format)
        self.sheet.write_number(1, 0, self.pillar_count, self.cell_format)
        self.sheet.write_string(0, 1, "1位端相机数量", self.cell_format)
        self.sheet.write_number(1, 1, len(self.first_side_set), self.cell_format)
        self.sheet.write_string(0, 2, "2位端相机数量", self.cell_format)
        self.sheet.write_number(1, 2, len(self.second_side_set), self.cell_format)
        self.sheet.set_column('A:D', 15)

        self.sheet.merge_range(5, 0, 5, 3, "数据统计", self.merge_format)
        self.sheet.write_string(6, 0, "数量", self.cell_format)
        self.sheet.write_string(6, 1, "位置", self.cell_format)
        self.sheet.write_string(6, 2, "统计", self.cell_format)
        self.sheet.write_string(6, 3, "百分比", self.cell_format)
        row = 7
        infos = {}
        for pillar in self.pillar_info:
            count = pillar.first + pillar.second
            if count in infos:
                infos[count].count += 1
            else:
                infos[count] = ImageInfo()
                infos[count].count = 1

            if count == 0:
                continue

            if pillar.second == 0:
                infos[count].from_first += 1
            elif pillar.first == 0:
                infos[count].from_second += 1
            else:
                infos[count].from_all += 1

        sorted(infos.items(), key=lambda item: item[1].count, reverse=True)
        for key in infos.keys():
            begin = row
            self.sheet.write_number(row, 0, key, self.merge_format)

            if key == 0:  # 没有支柱图片的文件夹
                self.sheet.write_string(row, 1, "数量", self.cell_format)
                self.sheet.write_number(row, 2, infos[key].count, self.cell_format)
                if infos[key].count != 0:
                    self.sheet.write_number(row, 3, infos[key].count / self.pillar_count, self.num_format)
                else:
                    self.sheet.write_string(row, 3, "", self.cell_format)

                row += 1
            else:
                self.sheet.write_string(row, 1, "一位端", self.cell_format)
                self.sheet.write_number(row, 2, infos[key].from_first, self.cell_format)
                if infos[key].from_first != 0:
                    self.sheet.write_number(row, 3, infos[key].from_first / self.pillar_count, self.num_format)
                else:
                    self.sheet.write_string(row, 3, "", self.cell_format)

                self.sheet.write_string(row+1, 1, "二位端", self.cell_format)
                self.sheet.write_number(row+1, 2, infos[key].from_second, self.cell_format)
                if infos[key].from_second != 0:
                    self.sheet.write_number(row+1, 3, infos[key].from_second / self.pillar_count, self.num_format)
                else:
                    self.sheet.write_string(row+1, 3, "", self.cell_format)

                self.sheet.write_string(row+2, 1, "一位端和二位端", self.cell_format)
                self.sheet.write_number(row+2, 2, infos[key].from_all, self.cell_format)
                if infos[key].from_all != 0:
                    self.sheet.write_number(row+2, 3, infos[key].from_all / self.pillar_count, self.num_format)
                else:
                    self.sheet.write_string(row+2, 3, "", self.cell_format)

                row += 3
                self.sheet.merge_range(begin, 0, row-1, 0, key, self.merge_format)

    def close(self):
        try:
            self.workbook.close()
            return True
        except Exception as ex:
            print(ex.args)
            return False

def json_file():
    script_path = os.path.abspath(__file__)
    if os.path.islink(script_path):
        real_path = os.path.realpath(script_path)
        dir = os.path.dirname(real_path)
    else:
        dir = os.path.dirname(script_path)
    return os.path.join(dir,"Camera.json")


if __name__ == '__main__':
    parse = argparse.ArgumentParser(description="数据统计")
    parse.add_argument("-i","--input",required=True,type=str,help="需要统计的数据文件夹路径")
    if len(sys.argv) < 2:
        parse.print_help()
    else:
        args = parse.parse_args()
        if os.path.isabs(args.input):
            target_path = args.input
        else:
            current = os.getcwd()
            target_path = os.path.abspath(args.input)
        
        dir_name = os.path.basename(target_path)
        if os.path.exists(target_path) and check_path(dir_name):
            output_path = os.path.join(target_path, dir_name + "_result.xlsx")
            if os.path.exists(output_path):
                os.remove(output_path)

            report = XlsxReport(output_path)
            report.write_report_header()
            report.start(target_path)
            report.write_total()
            report.statistics()
            report.close()
            print("完成:{}".format(output_path))
        else:
            print("数据文件夹不存在或格式不匹配")
        
        
    
    
